package com.dotflat.check.repository.Impl;

import com.dotflat.check.domain.C.PO.GetAttendanceCardResult;
import com.dotflat.check.domain.C.PO.GetAttendanceUserResult;
import com.dotflat.check.domain.C.PO.GetClassListInfoResult;
import com.dotflat.check.domain.C.PO.GetGuardianListInfo2Result;
import com.dotflat.check.po.CardPO;
import com.dotflat.check.po.CheckUserPO;
import com.dotflat.check.po.UserCardInfoPO;
import com.dotflat.check.po.UserRelationCardInfoPO;
import com.dotflat.check.repository.CardInfoRepository;
import com.matt.commons.page.PagerQuery;
import com.matt.foundation.DO.ChildrenCourseItemDO;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/11
 * *******************************
 */
@Service
public class CardInfoRepositoryImpl extends BaseNativeSqlRepository implements CardInfoRepository {

    @Override
    public List<UserCardInfoPO> getUserCardInfoPO(String areaId, int status, String classId, PagerQuery pagerQuery) {

        String querySql = "SELECT c.id as childrenId,c.`name` as childrenName,ifnull(cs.name,'') className,ifnull(cc.cardNo,'') cardNo," +
                "ifnull(cc.cardCode,'') as cardCode,cei.checkStatus  as `status`,ifnull(DATE_FORMAT( cc.created, '%Y-%m-%d'),'') cardTime  FROM children c  " +
                " LEFT JOIN rel_classes_children rcc on c.id = rcc.childrenId  " +
                " LEFT JOIN classes cs on cs.id = rcc.classesId " +
                " LEFT JOIN  c_check_card cc on cc.userId = c.id and cc.type = 1 " +
                " LEFT JOIN children_extend_info cei on cei.childrenId = c.id ";

        querySql += String.format(" WHERE c.aid = '%s' ", areaId);

        if (StringUtils.isNotBlank(classId)) {
            querySql += String.format(" and cs.id ='%s' ", classId);
        }
        if (status > 0) {
            if (status == 1) {
                querySql += "  and cc.cardNo is not NULL";
            }
            if (status == 2) {
                querySql += "  and cc.cardNo is  NULL";
            }
        }
        querySql += String.format(" limit %s, %s ", pagerQuery.getPageNum() * pagerQuery.getPageSize(), pagerQuery.getPageSize());
        List<UserCardInfoPO> objectArrayList = sqlObjectList(querySql, UserCardInfoPO.class);
        return objectArrayList;
    }

    @Override
    public Integer getUserCardInfoCount(String areaId, int status, String classId) {
        String querySql = String.format("SELECT COUNT(c.id)  FROM children c  " +
                " LEFT JOIN rel_classes_children rcc on c.id = rcc.childrenId  " +
                " LEFT JOIN classes cs on cs.id = rcc.classesId " +
                " LEFT JOIN  c_check_card cc on cc.userId = c.id and cc.type = 1 " +
                " LEFT JOIN children_extend_info cei on cei.childrenId = c.id " +
                " WHERE c.aid = '%s' ", areaId);
        if (StringUtils.isNotBlank(classId)) {
            querySql += String.format(" and cs.id ='%s' ", classId);
        }
        if (status > 0) {
            if (status == 1) {
                querySql += "  and cc.cardNo is not NULL";
            }
            if (status == 2) {
                querySql += "  and cc.cardNo is  NULL";
            }
        }
        Long count = sqlSingleResult(querySql);
        return Math.toIntExact(count);
    }

    @Override
    public List<UserRelationCardInfoPO> getUserRelationCardInfoPO(String areaId, int status, String classId, PagerQuery pagerQuery) {
        String querySql = "SELECT c.id as childrenId,c.`name` as childrenName,ifnull(cs.name,'') className,ifnull(cc.cardNo,'') cardNo,  " +
                "ifnull(cc.cardCode,'') as cardCode,cei.checkStatus  as `status`,ifnull(DATE_FORMAT( cc.created, '%Y-%m-%d'),'') cardTime   " +
                ",ifnull(u.relation,'') relation ,ifnull(u.userName ,'') userName,ifnull(u.id ,'') userId  " +
                " FROM children c   LEFT JOIN rel_classes_children rcc on c.id = rcc.childrenId   LEFT JOIN classes cs on cs.id = rcc.classesId  LEFT JOIN   " +
                " c_check_card cc on cc.relationUserId = c.id and cc.type = 3  LEFT JOIN children_extend_info cei on cei.childrenId = c.id   " +
                " LEFT JOIN (SELECT uc.cid ,u.id,u.`name` userName,uc.relation  FROM userchildren uc LEFT JOIN users u on u.id = uc.uid" +
                " WHERE u.id is not null GROUP BY uc.uid   " +
                ") u on u.cid = c.id ";

        querySql += String.format(" WHERE c.aid = '%s'   and u.id is not null  ", areaId);

        if (StringUtils.isNotBlank(classId)) {
            querySql += String.format(" and cs.id ='%s' ", classId);
        }
        if (status > 0) {
            if (status == 1) {
                querySql += "  and cc.cardNo is not NULL";
            }
            if (status == 2) {
                querySql += "  and cc.cardNo is  NULL";
            }
        }
        querySql += String.format(" limit %s, %s ", pagerQuery.getPageNum() * pagerQuery.getPageSize(), pagerQuery.getPageSize());
        List<UserRelationCardInfoPO> objectArrayList = sqlObjectList(querySql, UserRelationCardInfoPO.class);
        return objectArrayList;
    }

    @Override
    public Integer getUserRelationCardInfoPOCount(String areaId, int status, String classId) {
        String querySql = "SELECT COUNT(c.id)  " +
                " FROM children c   LEFT JOIN rel_classes_children rcc on c.id = rcc.childrenId   LEFT JOIN classes cs on cs.id = rcc.classesId  LEFT JOIN   " +
                " c_check_card cc on cc.relationUserId = c.id and cc.type = 3  LEFT JOIN children_extend_info cei on cei.childrenId = c.id   " +
                " LEFT JOIN (SELECT uc.cid ,u.id,u.`name` userName,uc.relation  FROM userchildren uc LEFT JOIN users u on u.id = uc.uid" +
                " WHERE u.id is not null GROUP BY uc.uid   " +
                ") u on u.cid = c.id ";

        querySql += String.format(" WHERE c.aid = '%s' ", areaId);

        if (StringUtils.isNotBlank(classId)) {
            querySql += String.format(" and cs.id ='%s' ", classId);
        }
        if (status > 0) {
            if (status == 1) {
                querySql += "  and cc.cardNo is not NULL";
            }
            if (status == 2) {
                querySql += "  and cc.cardNo is  NULL";
            }
        }
        Long count = sqlSingleResult(querySql);
        return Math.toIntExact(count);
    }

    @Override
    public UserRelationCardInfoPO getUserRelationCardInfo_v2(String areaId, String childrenId, String userId, String cardNo) {
        String querySql = "SELECT c.id as childrenId,c.`name` as childrenName,ifnull(cs.name,'') className,ifnull(cc.cardNo,'') cardNo,  " +
                "ifnull(cc.cardCode,'') as cardCode,cei.checkStatus  as `status`,ifnull(DATE_FORMAT( cc.created, '%Y-%m-%d'),'') cardTime   " +
                ",ifnull(u.relation,'') relation ,ifnull(u.userNmae ,'') userName,ifnull(u.id ,'') userId  " +
                " FROM children c   LEFT JOIN rel_classes_children rcc on c.id = rcc.childrenId   LEFT JOIN classes cs on cs.id = rcc.classesId  LEFT JOIN   " +
                " c_check_card cc";
        if (StringUtils.isNotBlank(childrenId)) {
            querySql += " on cc.userId = c.id and cc.type = 1 ";
        } else {
            querySql += " on cc.relationUserId = c.id and cc.type = 3 ";
        }

        querySql += " LEFT JOIN children_extend_info cei on cei.childrenId = c.id   " +
                " LEFT JOIN (SELECT uc.cid ,u.id,u.`name` userNmae,uc.relation  FROM userchildren uc LEFT JOIN users u on u.id = uc.uid" +
                " WHERE u.id is not null GROUP BY uc.uid   " +
                ") u on u.cid = c.id ";
        querySql += String.format(" WHERE c.aid = '%s' ", areaId);
        if (StringUtils.isNotBlank(childrenId)) {
            querySql += String.format(" and c.id ='%s' ", childrenId);
        }

        if (StringUtils.isNotBlank(userId)) {
            querySql += String.format(" and u.id ='%s' ", userId);
        }
        querySql += String.format(" and cc.cardNo ='%s' ", cardNo);
        List<UserRelationCardInfoPO> objectArrayList = sqlObjectList(querySql, UserRelationCardInfoPO.class);
        if (objectArrayList.size() > 0) {
            return objectArrayList.get(0);
        }
        return null;
    }

    @Override
    public List<GetAttendanceUserResult> getKgIdInfo(String kgId, String modifyTime) {
        String querySql = "SELECT  DATE_FORMAT( c.birthday, '%Y-%m-%d %H:%i:%S') as BirthDay,IFNULL(cs.id,'') as ClassInfoID,IFNULL(cs.`name`,'') as ClassName,  " +
                "  c.gender as Sex,c.id as UserId, c.`status` as State, " +
                "  c.name as UserName,cr.userType as UserType FROM children c LEFT JOIN check_record cr on c.id = cr.childrenId and cr.userType = 1   " +
                " LEFT JOIN rel_classes_children rcc ON rcc.childrenId = c.id   " +
                " LEFT JOIN classes cs on cs.id = rcc.classesId";
        querySql += String.format(" WHERE c.aid = '%s'   ", kgId);
//        querySql += " and DATE_FORMAT( cr.created, '%Y-%m-%d') = DATE_FORMAT( '"+modifyTime+"', '%Y-%m-%d') ";
        List<GetAttendanceUserResult> objectArrayList = sqlObjectList(querySql, GetAttendanceUserResult.class);
        return objectArrayList;
    }

    @Override
    public List<GetAttendanceUserResult> getKgIdByUsers(String kgId) {
        String querySql = "SELECT u.id as UserId,u.`name` as UserName,IFNULL(cs.id,'0') as ClassInfoID," +
                "IFNULL(cs.`name`,'') as ClassName, uc.relation as BroadcastName  " +
                "    FROM users u " +
                "LEFT JOIN userchildren uc on u.id = uc.uid  LEFT JOIN children c on c.id =uc.cid " +
                " LEFT JOIN rel_classes_children rcc ON rcc.childrenId = c.id  " +
                " LEFT JOIN classes cs on cs.id = rcc.classesId   ";
        querySql += String.format("  WHERE  c.aid = '%s'   ", kgId);
        List<GetAttendanceUserResult> objectArrayList = sqlObjectList(querySql, GetAttendanceUserResult.class);
        objectArrayList.stream().forEach(o->{
            if (StringUtils.equals("爸爸", o.getBroadcastName()) ||
                    StringUtils.equals("爷爷", o.getBroadcastName())  ||
                    StringUtils.equals("姥爷", o.getBroadcastName()) ){
                o.setSex(1);
            }
            o.setBroadcastName("");
        });

        return objectArrayList;
    }

    @Override
    public List<GetClassListInfoResult> getKgIdByClassListInfo(String kgId) {
        String querySql = "SELECT c.name as ClassName ,c.id as ClassInfoID ,c.limitNum as Capacity,c.created as CreateDate" +
                " FROM classes c  ";
        querySql += String.format("  WHERE  c.areaId = '%s'    ", kgId);
        List<GetClassListInfoResult> objectArrayList = sqlObjectList(querySql, GetClassListInfoResult.class);
        return objectArrayList;
    }

    @Override
    public List<GetGuardianListInfo2Result> getKgIdByGuardianListInfo(String kgId, String modifyTime) {
        String querySql = "SELECT u.id as UserId,u.`name` as Name,u.phone as Phone,uc.relation as RelationshipName,c.id as ChildId," +
                "c.birthday as Birth  FROM users u " +
                "LEFT JOIN userchildren uc on u.id = uc.uid  LEFT JOIN children c on c.id =uc.cid  ";
        querySql += String.format("  WHERE  c.aid = '%s'  ", kgId);
//        querySql += " and DATE_FORMAT( c.birthday, '%Y-%m-%d') = DATE_FORMAT( '"+modifyTime+"', '%Y-%m-%d') ";
        querySql += " GROUP BY u.id ,c.id";
        List<GetGuardianListInfo2Result> objectArrayList = sqlObjectList(querySql, GetGuardianListInfo2Result.class);

        objectArrayList.stream().forEach(o->{
            if (StringUtils.equals("爸爸", o.getRelationshipName()) ||
                    StringUtils.equals("爷爷", o.getRelationshipName())  ||
                    StringUtils.equals("姥爷", o.getRelationshipName()) ){
                o.setSex(1);
            }
        });
        return objectArrayList;
    }

    @Override
    public List<GetAttendanceCardResult> getGuardianListInfo2Result(String kgId) {
        String querySql = "SELECT ccc.cardCode as CardNo,ccc.userId as UserId,ccc.created as CreateTime,ccc.type as CardType " +
                " FROM c_check_card ccc  ";
        querySql += String.format("  WHERE  ccc.areaId = '%s'  ", kgId);
        List<GetAttendanceCardResult> objectArrayList = sqlObjectList(querySql, GetAttendanceCardResult.class);
        return objectArrayList;
    }

    @Override
    public List<CheckUserPO> getCheckUserPO(String areaId, Integer type, Integer whole) {
        String sql = "SELECT u.id as userId ,u.name as userName,ifnull(u.headImgUrl,'') as headImage,ifnull(cc.cardCode,'') as sACardNo " +
                " FROM userchildren uc LEFT JOIN children c  on c.id = uc.cid LEFT JOIN users u ON u.id = uc.uid " +
                " LEFT JOIN c_check_card cc ON cc.userId = u.id  AND cc.relationUserId = c.id  and cc.type= '" + type + "' " +
                " WHERE c.aid = '" + areaId + "' ";
        if (whole == 1) {
            sql = "SELECT u.id as userId ,u.name as userName,ifnull(u.headImgUrl,'') as headImage,cc.cardCode as sACardNo   " +
                    " FROM c_check_card cc LEFT JOIN userchildren uc  ON cc.userId = uc.uid  AND cc.relationUserId = uc.cid " +
                    " LEFT JOIN children c  on c.id = uc.cid LEFT JOIN users u ON u.id = uc.uid " +
                    " WHERE  c.aid  = '" + areaId + "' AND cc.type= '" + type + "' ";
        }
        List<CheckUserPO> objectArrayList = sqlObjectList(sql, CheckUserPO.class);
        return objectArrayList;
    }

    @Override
    public List<CheckUserPO> getCheckUserByChildren(String areaId, Integer type, Integer whole) {
        if (type == 1 || type == 0) {
            String sql = "  SELECT DATE_FORMAT( c.birthday, '%Y-%m-%d %H:%i:%S') as airthDay, c.aid as kgId, ifnull(cs.id,'') as classInfoID  , ifnull(cs.name,'') as className  ,   " +
                    " ifnull(c.imgUrl,'') as headImage ,c.name as userName,  c.id as userId , c.gender as sex ,  c.status as state,ifnull(cc.cardCode,'') as sACardNo    FROM children  c   " +
                    " LEFT JOIN c_check_card cc on c.id = cc.userId and cc.type=  '" + type + "'  " +
                    " LEFT JOIN  rel_classes_children rcc on rcc.childrenId = c.id  " +
                    " LEFT JOIN classes cs on rcc.classesId = cs.id " +
                    "  WHERE c.aid = '" + areaId + "' " +
                    "  GROUP BY c.id";

            if (whole == 1) {
                sql = " SELECT  DATE_FORMAT( c.birthday, '%Y-%m-%d %H:%i:%S') as airthDay, c.aid as kgId, ifnull(cs.id,'') as classInfoID  , ifnull(cs.name,'') as className  ,   " +
                        " ifnull(c.imgUrl,'') as headImage ,c.name as userName,  c.id as userId , c.gender as sex ,  c.status as state,ifnull(cc.cardCode,'') as sACardNo     " +
                        " FROM c_check_card cc  LEFT JOIN  children  c  on c.id =cc.userId  LEFT JOIN  rel_classes_children rcc on rcc.childrenId = c.id   " +
                        " LEFT JOIN classes cs on rcc.classesId = cs.id  " +
                        " LEFT JOIN userchildren uc on uc.cid = c.id  " +
                        " LEFT JOIN users u on uc.uid = u.id  " +
                        " WHERE  c.aid  = '" + areaId + " ' and cc.type=  '" + type + "' " +
                        " GROUP BY c.id";
            }
            List<CheckUserPO> objectArrayList = sqlObjectList(sql, CheckUserPO.class);
            return objectArrayList;
        }
       return new ArrayList<>();
    }

    @Override
    public List<CardPO> getAreaIdByChildrenCardList(String areaId, Integer type, Integer whole) {
        if (type == 1 || type == 0) {
            String sql = "SELECT  ifnull(cc.created  ,null) as createTime, c.aid as kgId ,c.id as userId ,  " +
                    " ifnull(cc.cardCode,'') as cardNo ,ifnull(cc.id,'') as id  " +
                    "  FROM children  c   " +
                    " LEFT JOIN c_check_card cc on c.id =cc.userId  and cc.relationUserId = c.id  and cc.type= '1'   " +
                    " WHERE c.aid = '" + areaId + "' " ;

            if (whole == 1) {
                sql += " and cc.cardNo is not null ";
            }
            sql += " GROUP BY c.id " ;
            List<CardPO> objectArrayList = sqlObjectList(sql, CardPO.class);
            return objectArrayList;
        }
        return new ArrayList<>();
    }

    @Override
    public List<CardPO> getAreaIdByUserCardList(String areaId, Integer type, Integer whole) {
        if (type == 1 || type == 0) {
            String sql = " SELECT ifnull(cc.created  ,null) as createTime, c.aid as kgId , u.id as userId,  " +
                    " ifnull(cc.cardCode,'') as cardNo, ifnull(cc.id,'') as id  " +
                    " FROM userchildren uc LEFT JOIN children c  on c.id = uc.cid LEFT JOIN users u ON u.id = uc.uid   " +
                    " LEFT JOIN c_check_card cc ON cc.userId = u.id  and cc.relationUserId = c.id   and  cc.type= '3' " +
                    " WHERE c.aid = '" + areaId + "'  " ;

            if (whole == 1) {
                sql += "  and cc.cardNo is not null ";
            }
            sql += " GROUP BY c.id " ;
            List<CardPO> objectArrayList = sqlObjectList(sql, CardPO.class);
            return objectArrayList;
        }
        return new ArrayList<>();
    }
}
